use master 
go
create database bbs
on
(
	name='bbs',
	filename='D:\bbs.mdf',
	size=10,
	maxsize=200,
	filegrowth=20
)
log on
(
	name='bbs_log',
	filename='D:\bbs_log.ldf ',
	size=10,
	maxsize=200,
	filegrowth=20
)
go
use bbs

create table stuInfo
(  
    stuld int primary key identity not null,
	stuname varchar(20) not null,
	stuage varchar(20) not null,
	stusex varchar(2) check (stusex in ('1','0'))not null,
	time datetime default('null')
)
go
create table courseInfo
(
   
   courseid int primary key identity  not null,
   coursename varchar(20) not null,
   coursemarks int not null
)
go
create table scoreInfo
(
    
	scoreid int primary key identity not null,
	stuid int references stuInfo(stuld)  not null,
	courseld int references courseInfo(courseid) not null,
	score int not null


)
go

 insert into stuInfo values ('tom','19','1',''),('jack','20','0',''),('rose','21','1',''),('lulu','19','1',''),('lili','21','0',''),('abc','20','1','2007-01-07')


 select * from stuInfo

 insert into courseInfo values('javabase','4'),('html','2'),('javascript','2'),('sqlbase','2')

 select * from courseInfo

 insert into scoreInfo values('1','1','80'),('1','2','85'),('1','4','50'),('2','1','75'),('2','3','45'),('2','4','75'),('3','1','45'),('4','1','95'),('4','2','75'),('4','3','90'),('4','4','45')

 select * from scoreInfo

 select stuid,stuname,count(courseld),avg(score) from scoreInfo inner join stuInfo on scoreInfo.stuid=stuInfo.stuld group by  stuid,stuname

 select scoreInfo.courseld,coursename,COUNT(stuid),SUM(score) from scoreInfo inner join courseInfo on scoreInfo.courseld=courseInfo.courseid group by scoreInfo.courseld,coursename

 select a.* from stuInfo a , stuInfo b where a.stusex=b.stusex and a.stuage=b.stuage and a.stuld!=b.stuld

 select distinct a.courseid,a.coursename,a.coursemarks from courseInfo a,courseInfo b where a.coursemarks=b.coursemarks and a.courseid!=b.courseid 

 select stuid,stuname,courseld,score from scoreInfo inner join stuInfo on stuInfo.stuld=scoreInfo.stuid group by stuid,stuname,courseld,score

 select stuid,courseInfo.courseid,coursename,coursemarks,score from scoreInfo inner join courseInfo on scoreInfo.courseld=courseInfo.courseid  group by stuid,courseInfo.courseid,coursename,coursemarks,score

 select * from  stuInfo left join scoreInfo on scoreInfo.stuid=stuInfo.stuld WHERE score IS NULL

select * from stuInfo where datepart(weekday,time)=1 or datepart(weekday,time)=7

 select * from stuInfo where stuname like '%a%'

 select stuid,count(courseld),AVG(score) from scoreInfo group by stuid having COUNT(courseld)>2 and AVG(score)>70

